<?php
class M_enrollees extends MY_Model {
	
	private $__table = 'enrollments';
	protected $_table = 'enrollments';
	protected $_uid = 'e_id';
	protected $_timestamp = true;
	protected $_created_at = 'e_created_at';
	protected $_updated_at = 'e_updated_at';
	
	public function __construct()
	{
		parent::__construct();
		$this->_salt = sha1('1oyYe3u%zN9$V2fwhP5BXm8G6aQd7Lri#SCtrkj0');
	}

	// used for getting section
	public function get_section_by_profile($profile_id){
	  $q = "SELECT e_section_id, e_id FROM enrollments WHERE e_id = ?";
	  
		$q = $this->db->query($q, array($profile_id));
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function get_student_fees($e_id,$sy='',$type = '')
	{
		$type = trim(strtolower($type));
		$fees = "SELECT  sef.sef_fee_name,
							sef.sef_fee_rate ,
							sef.sef_id
					FROM student_enrollment_fees sef
					where sef.sef_enrollment_id = ?
					AND sef.sef_schoolyear_id = ? ";
					
		if($type == 'misc'){
			$fees .= " AND sef.is_misc_fee = 1 ";
		}elseif($type == 'other'){
			$fees .= " AND sef.is_other_fee = 1 ";
		}elseif($type == 'tuit'){
			$fees .= " AND sef.is_tuition_fee = 1 ";
		}elseif($type == 'elec'){
			$fees .= " AND sef.is_elective_fee = 1 ";
		}elseif($type == 'uniform'){
			$fees .= " AND sef.is_uniform_fee = 1 ";
		}elseif($type == 'cca'){
			$fees .= " AND sef.is_cca_fee = 1 ";
		}
		
		$get_fees = $this->db->query($fees,array($e_id,$sy));
		// pd($this->db->last_query());
		return $get_fees->num_rows() > 0 ? $get_fees->result() : FALSE;
	}
	
	public function xget_enrollee_prof($e_id)
	{
		$sql= "SELECT concat(p.firstname,' ',p.lastname) as fullname,
					  e.e_student_id as idno,
					  l.level_desc,
					  l.level_code,
					  r.description,
					  r.name as room_name,
					  p.profile_id,
					  e.e_id,
					  l.level_id,
					  l.grade_type,
					  s.section_name,
					  u.id as u_id,
					  u.username as u_username,
					  p.email,
					  l.payment_group
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN sections s ON s.id = e.e_section_id
				LEFT JOIN rooms r ON r.section_id = s.id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				LEFT JOIN users u ON u.id = p.user_id
				WHERE e.e_id = ?";
		$query = $this->db->query($sql,array($e_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}

	public function get_enrollee_prof($e_id)
	{
		$sql= "SELECT concat(p.firstname,' ',p.lastname) as fullname,
					  e.e_student_id as idno,
					  l.level_desc,
					  r.description,
					  r.name as room_name,
					  p.profile_id,
					  e.e_id,
					  l.level_id,
					  l.grade_type,
					  s.section_name,
					  s.id as section_id,
					  u.id as u_id,
					  u.username as u_username,
					  p.email,
					  l.payment_group,
					  p.nationality,
					  e.finance_show_grades,
					  e.adviser_show_grades,
					  e.academichead_show_grades,
					  e.can_login,
					  p.gender
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN sections s ON s.id = e.e_section_id
				LEFT JOIN rooms r ON r.section_id = s.id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				LEFT JOIN users u ON u.id = p.user_id
				WHERE e.e_id = ?";
		$query = $this->db->query($sql,array($e_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	public function get_enrollments_prof($pid)
	{
		$sql = "SELECT sy.sy_from, sy.sy_to, sy.desc, e.e_id, e.e_profile_id
				FROM enrollments e
				LEFT JOIN school_years sy ON sy.id = e.e_ay_id
				WHERE e.e_profile_id = ?
				ORDER BY e.e_id DESC
			   ";
			   
		$query = $this->db->query($sql,[$pid]);
		
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_sum_of_paid_fees($id,$sy)
	{
	
		$sql = 'SELECT sum(spr_ammt_paid) as spr_ammt_paid
			   FROM student_payment_records
			   WHERE spr_schoolyear_id = ?
			   AND spr_enrollment_id = ?
			   AND spr_is_deleted = 0';
	
		$query = $this->db->query($sql,array($sy,$id));
		if($query->num_rows() > 0)
		{
			$row = $query->row();
			return $row->spr_ammt_paid;
		}else
		{
			return 'No Data';
		}
	}

	public function get_sum_of_total_payables($id,$ay)
	{
		$sql = 'SELECT sum(spm_value) as total_charge
				FROM student_payment_modes
				WHERE spm_student_id = ?
				AND spm_ay_id = ?';
		
		$total_ = $this->db->query($sql,array($id,$ay));
		if($total_->num_rows() >= 1)
		{
			return $total_->row()->total_charge;
		}else{
			return 0;
		}
	}

	public function get_sum_of_student_electives($e_id,$sy)
	{
		$q = $this->db->select('sum(sef_fee_rate) as total_electives',FALSE)
				 ->where('sef_enrollment_id',$e_id)
				 ->where('sef_schoolyear_id',$sy)
				 ->where('is_elective_fee',1)
				 ->get('student_enrollment_fees');
		return $q->num_rows() >= 1 ? $q->row()->total_electives : 0;	 
	}

	public function check_student_nationality($e_id)
	{
		$enrollee_data = $this->get_enrollee_prof($e_id);
		// pd($enrollee_data);
		
		//if nationality is not filipino
		//check if fees are applied
		if(strtolower($enrollee_data->nationality) !== 'filipino')
		{
			$sql = "SELECT count(is_elective_fee) AS all_electives 
					FROM student_enrollment_fees
					WHERE sef_enrollment_id = ?
					AND sef_schoolyear_id = ? 
					AND is_elective_fee = 1";
			$query = $this->db->query($sql,array($e_id,$this->c_sy->id)); 
			
			$get_electives = "SELECT * 
							  FROM foreign_fee
							  WHERE level_id = ?";
			$fees = $this->db->query($get_electives,array($enrollee_data->level_id));
			
			
			if($query->num_rows() >=1)
			{
				$result = $query->row()->all_electives;
				$foreign_fee = $fees->result();
				if($result >= $fees->num_rows())
				{
					return FALSE;
				}else{
					return TRUE;
				}
			}else{
				return FALSE;
			}
		}else{
			return FALSE;
		}
	}
	
	public function get_sum_of_amount_due($id,$ay)
	{
		$sql = "SELECT sum(sef_fee_rate) as total_due 
				FROM student_enrollment_fees 
				WHERE sef_enrollment_id = ? AND sef_schoolyear_id = ?";
				
		$sql2 = "SELECT sum(sdr_amount) as total_deductions
				FROM student_deductions_record
				WHERE sdr_enrollment_id = ?
				AND sdr_schoolyear_id = ? ";
		$query = $this->db->query($sql,array($id,$ay));
		$query2 = $this->db->query($sql2,array($id,$ay));
		$total_amount_due =  $query->num_rows() > 0 ? $query->row()->total_due : 0;
		$total_student_deductions = $query2->num_rows() > 0 ? $query2->row()->total_deductions : 0;
		return ($total_amount_due - $total_student_deductions);
	}
	
	public function get_student_payment_mode($wazzup_id = FALSE,$sy_id = FALSE)
	{
		if($wazzup_id == FALSE AND !is_numeric($wazzup_id))
		{
			return FALSE;
		}else{
			$sql = 'SELECT payment_mode 
					FROM enrollments
					WHERE e_id = ?
					AND verified = 1
					AND e_archived = 0
					AND e_ay_id = ?';
			
			if($sy_id){
				$query = $this->db->query($sql,array($wazzup_id,$sy_id));
			}else{
				$query = $this->db->query($sql,array($wazzup_id,$this->c_sy->id));
			}
			
			if($query->num_rows() >= 1)
			{
				$result = $query->row()->payment_mode;
				if($result == '' OR $result == NULL OR $result == 'none')
				{
					return FALSE;
				}else{
					return $result;
				}
			}else{
				return FALSE;
			}
		}
	}
	
	public function get_otcp($id)
	{
		$query = $this->db->where('enrollment_id',$id)
				 ->where('schoolyear_id',$this->c_sy->id)
				 ->get('fees_over_the_counter');
	
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function get_all_schedule_by_student_subjects($enroll_id = false)
	{
		// $ci =& get_instance();
		
		// $ci->load->model('M_enrollees','me');
		
		// $ci->get_enrollee_prof->
		
		$select = "SELECT sub.subj_code,
						  sub.subj_desc,
						  concat(sch.time_from,' - ',sch.time_to) as time,
						  sch.day,
						  concat(emp.first_name,' ',emp.last_name) as teacher_name
				   FROM student_subjects ss
				   LEFT JOIN enrollments e ON ss.enrollment_id = e.e_id
				   LEFT JOIN subjects sub ON sub.subj_id = ss.subject_id
				   LEFT JOIN schedule sch ON sch.subject_id = sub.subj_id AND sch.section_id = e.e_section_id
				   LEFT JOIN employees emp ON sch.teacher_id = emp.emp_id
				   WHERE ss.enrollment_id = ?";
				   
		$q = $this->db->query($select,array($enroll_id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
		
	public function get_student_current_payment_mode_and_due_by_enrollment_id($e_id)
	{
		$sql = 'SELECT spm.*
				FROM enrollments e
				LEFT JOIN student_payment_modes spm ON spm.spm_student_id = e.e_id
				WHERE e.verified = 1
				AND e.payment_mode != "none"
				AND e.e_archived = 0
				AND spm.spm_value != 0.00
				AND e.e_id = ?
				ORDER BY field(month_name,"December","January","February","March","April","May","June","July","August","September","October","November")';
				
		$res = $this->db->query($sql,array($e_id));

		return $res->num_rows() >=1 ? $res->result() : FALSE;
	}
	
	public function get_raw_password($id, $usertype = 'student')
	{
		$q = "
			SELECT 
			AES_DECRYPT(enc_password,'$this->_salt') as raw_password
			FROM user_username_password
			WHERE user_id = ?
			AND usertype = ?
			LIMIT 1
		";

		$qry = $this->db->query($q, [$id, $usertype]);
	
		return $qry->num_rows() >=1 ? $qry->row()->raw_password : FALSE;
	}

	public function _update_to_username_password_table($_user_id,$_raw_password, $usertype = 'student')
	{
		$raw_password = trim($_raw_password);
		$raw_username = $_user_id;
		$usertype = $usertype == 'user' ? 'guardian' : $usertype;
		
		$check = 'SELECT * FROM user_username_password WHERE user_id = ?';
		$run_check = $this->db->query($check,[$_user_id]);

		if($run_check->num_rows() >= 1)
		{
			$sql = "update user_username_password
					SET enc_password = AES_ENCRYPT(?,?),
					updated = ?
					WHERE user_id = ?";
		
			$this->db->query($sql,array($raw_password,$this->_salt,NOW,$_user_id));
		}else{
			$this->_insert_to_username_password_table($_user_id,$_raw_password, $usertype);
		}
	}

	public function _insert_to_username_password_table($_user_id,$_raw_password, $usertype = 'student')
	{
		$raw_password = trim($_raw_password);
		$raw_username = $_user_id;
		
		$sql = "INSERT INTO user_username_password(user_id,enc_password,created,usertype)
				VALUES (?,AES_ENCRYPT('{$raw_password}','{$this->_salt}'),now(),?)";
		$this->db->query($sql,[$_user_id, $usertype]);
	}

	public function get_previous_e_id($e_id)
	{
		$sql = "SELECT e_id
				FROM enrollments
				WHERE e_ay_id = ?
				AND e_profile_id IN 
				( SELECT e_profile_id
				  FROM enrollments
				  WHERE e_id = ?
				)
			   ";
		
		$query = $this->db->query($sql,array(2,$e_id));
		
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
  
  public function get_sy_id($e_id)
	{
		$sql = "SELECT e_ay_id
				FROM enrollments
				WHERE e_id = ?";
		
		$query = $this->db->query($sql,array($e_id));
		
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	public function check_duplicate($p_id=FALSE)
	{
		$sy = $this->o_sy;
		$ay_id = $sy->id;
		
		if($p_id)
		{
			$q = $this->db->select('e_id')
						  ->where('e_profile_id',$p_id)
						  ->where('e_ay_id',$ay_id)
						  ->get('enrollments');
			
			return $q->num_rows() >=1 ? TRUE : FALSE;

		}else{
			return FALSE;
		}
	}
}	
?>
